﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Text;

namespace Savage.DotNetLibrary.DatabaseLibrary
{
    public class SqlBatch
    {
        public SqlBatch(string connectionString)
        {
            _sqlDataWrapper = new SqlDataWrapper(connectionString);
        }

        private SqlDataWrapper _sqlDataWrapper;


        /// <summary>
        /// Execute the SQL commands contained saved in a file.
        /// </summary>
        /// <param name="filename">The .sql file containing the SQL commands to execute.</param>
        public void ExecuteSqlBatchFile(string filename)
        {
            ExecuteSqlBatchFiles(new string[] { filename });
        }

        /// <summary>
        /// Executes multiple .sql file's.
        /// </summary>
        /// <param name="fileNames">A string array of .sql files to execute.</param>
        public void ExecuteSqlBatchFiles(string[] fileNames)
        {
            SqlConnection connection = _sqlDataWrapper.CreateConnection();
            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                foreach (string fileName in fileNames)
                {
                    using (StreamReader reader = new StreamReader(fileName))
                    {
                        foreach (string sql in SplitOnGoCommand(reader.ReadToEnd()))
                        {
                            _sqlDataWrapper.ExecuteTransactionalSqlCommand(transaction, sql);
                        }
                    }
                }
                //Commit the transaction
                transaction.Commit();
            }
            catch (Exception ex)
            {
                //Rollback and rethrow
                if (transaction != null)
                    transaction.Rollback();
                throw new Exception("Exception when executing SQL batches in transaction. Any previous SQL has been rolled back.", ex);
            }
            finally
            {
                connection.Close();
            }
        }

        public void ExecuteSqlBatch(SqlTransaction transaction, string sqlCommand)
        {
            foreach (string sql in SplitOnGoCommand(sqlCommand))
            {
                _sqlDataWrapper.ExecuteTransactionalSqlCommand(transaction, sql);
            }
        }

        public void ExecuteSqlBatch(string sqlCommand)
        {
            ExecuteSqlBatches(new string[] { sqlCommand });
        }

        public void ExecuteSqlBatches(string[] sqlCommands)
        {
            SqlConnection connection = _sqlDataWrapper.CreateConnection();
            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();

                //Run these inside a transaction
                foreach (string sqlCommand in sqlCommands)
                {
                    foreach (string sql in SplitOnGoCommand(sqlCommand))
                    {
                        if (sql.Trim().Length > 0)
                        {
                            _sqlDataWrapper.ExecuteTransactionalSqlCommand(transaction, sql);
                        }
                    }
                }

                //Commit the transaction
                transaction.Commit();
            }
            catch (Exception ex)
            {
                //Rollback and rethrow
                if (transaction!=null)
                    transaction.Rollback();
                throw new Exception("Exception when executing SQL batches in transaction. Any previous SQL has been rolled back.", ex);
            }
            finally
            {
                connection.Close();
            }
        }

        private string[] SplitOnGoCommand(string sql)
        {
            List<string> result = new List<string>();

            string[] lines = sql.Split('\n');
            StringBuilder sqlCommand = new StringBuilder();
            foreach (string line in lines)
            {
                if (String.Compare(line.Trim(), "GO", StringComparison.OrdinalIgnoreCase) == 0)
                {
                    //The command has ended we now need to store the sql
                    result.Add(sqlCommand.ToString());
   
                    //Create a new string builder once the sql has been executed
                    sqlCommand = new StringBuilder();
                }
                else
                {
                    sqlCommand.Append(line.Trim());
                    sqlCommand.Append("\r\n");
                }
            }
            //Often the last statement might not have a GO after it-we'll check and execute if this is the case
            if (sqlCommand.Length > 0)
            {
                result.Add(sqlCommand.ToString());
            }

            return result.ToArray();
        }
    }
}
